/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package DA;

import BO.OrdenDeViaticos;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author David
 */
public class DAOrden {
    
       String conexionString ="jdbc:sqlserver://localhost:1433;" +
            "databaseName=Viaticos;user=sa; password=admin;";           


    
    Connection con = null;
    PreparedStatement pstmt = null;
    Statement stmt= null;
    ResultSet rs = null;  
    
    
    public final Connection obtenerConexion() throws ClassNotFoundException
    {
         try {
             Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
             con = DriverManager.getConnection(conexionString);
         } catch (SQLException ex) {
             Logger.getLogger(DAOrden.class.getName()).log(Level.SEVERE, null, ex);
         }
         
         return con;
    }
    
   public final boolean InsertarOrdenDeViaticos(OrdenDeViaticos orden) throws SQLException, ClassNotFoundException, ParseException
    {
                 
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conexionString);
            
            String SQL = "insert into Orden_De_Viaticos values(?,?,?,GETDATE(),?,?,?,?,?,0,'En Proceso',?,?,?,?);";
            pstmt = con.prepareStatement(SQL);
            pstmt.setInt(1, orden.getNumero());
            pstmt.setInt(2, orden.getNumeroAnticipo());
            pstmt.setString(3, orden.getBanco());
            pstmt.setDouble(4, orden.getMonto_Anticipo());
            pstmt.setString(5, orden.getObjetivo());
            pstmt.setString(6, orden.getObservaciones());
            pstmt.setInt(7, orden.getPagueseA());
            pstmt.setInt(8, orden.getJefe());
            pstmt.setDouble(9, orden.getValorTotal());
            pstmt.setDouble(10, orden.getDiferencia());
             String str = orden.getFechaReintegro();
            DateFormat formatter ; 
             
            formatter = new SimpleDateFormat("dd/MM/yy");
            java.util.Date date = formatter.parse(str);
            pstmt.setDate(11, convertJavaDateToSqlDate(date));
            pstmt.setInt(12, orden.getReciboIntegro());
           
            
            pstmt.executeUpdate();
      
            if (pstmt != null) {
                pstmt.close();
            }
        
        
    return true;
        
    }
   
    
    public boolean existeOrden(int id) throws SQLException, ClassNotFoundException
    {
       int numero=0;
   
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        con = DriverManager.getConnection(conexionString);
        String SQL = "select Numero from Orden_De_Viaticos where Numero=?;"; 
        pstmt = con.prepareStatement(SQL);
        pstmt.setInt(1,id);
        rs = pstmt.executeQuery();
        
        while ( rs.next() ) {
            numero = rs.getInt("Numero"); 
        }
        con.close();
   
    
    return numero!=0;
    
   
        
    }
    public int Obtener_Consecutivo () throws SQLException{
           int numero=0;   
        try {
    
               Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
               con = DriverManager.getConnection(conexionString);
               String SQL = "select count(Numero) from Orden_De_Viaticos;";
               pstmt = con.prepareStatement(SQL);
               rs = pstmt.executeQuery();
               
               while ( rs.next() ) {
                   numero = rs.getInt(1);
}
               con.close();
               
           } catch (ClassNotFoundException ex) {
               Logger.getLogger(DAOrden.class.getName()).log(Level.SEVERE, null, ex);
           }
           return numero;
    }
    


    public final OrdenDeViaticos buscarOrden(int numero) throws SQLException, ClassNotFoundException
    {
        OrdenDeViaticos orden= new OrdenDeViaticos();
        if(this.existeOrden(numero))
        {
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        con = DriverManager.getConnection(conexionString);
        String SQL = "select  Numero,Objetivo,ValorTotal,Anulado from orden_de_viaticos where Numero=?"; 
        pstmt = con.prepareStatement(SQL);
        pstmt.setInt(1,numero);
        rs = pstmt.executeQuery();
        
        while ( rs.next() ) {
            orden.setNumero(rs.getInt("Numero"));
            orden.setObjetivo(rs.getString("Objetivo"));
            orden.setValorTotal(rs.getDouble("ValorTotal"));
            orden.setAnulado(rs.getInt("Anulado"));
            
        }
        con.close();
   
            return orden;
        }
        else
        {
            return null;
        }
    }
    

      public java.sql.Date convertJavaDateToSqlDate(java.util.Date date) {
    return new java.sql.Date(date.getTime());
}

    public  void anularViatico(int numero) throws ClassNotFoundException, SQLException
    {
          Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = DriverManager.getConnection(conexionString);
            
            String SQL = "update Orden_De_Viaticos\n" +
                          "set Anulado=1 "+
                          "where Numero=?;";
            pstmt = con.prepareStatement(SQL);
            pstmt.setInt(1,numero);
            pstmt.executeUpdate();
    }
    

}
